﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.SqlClient;
using MobileCommissionManagement.Common;
using MobileCommissionManagement.Framework;

namespace MobileCommissionManagement
{
    public class VodafoneController
    {
        private readonly VodafoneEntities _context;
        private readonly string _connectionString;

        public VodafoneController()
        {
            _connectionString = GetConnectionString("Vodafone");
            _context = new VodafoneEntities(new EntityConnection(GetConnectionString("VodafoneEntities")));
            _context.Connection.Open();
        }
        
        #region Private Const

        private const string QDealerColumnsName =
            @"  select	col.name as name, 
                        col.system_type_id as type 
                from	sys.columns col, sys.tables tab
                where	col.object_id = tab.object_id 
		                and tab.name = @TableName";

        private const string QDealerFiltered =
            @"  select	*
                from	{0}
                where	TipoNegocio like '%@BusinessType%'
                        and LinhaNegocio like '%@BusinessLine%'
                        and AnoMes = @YearMonth";


        #endregion

        #region public Properties

        public VodafoneEntities Context
        {
            get   { return  _context; }
        }

        public ObjectQuery<TipoNegocio> BusinessTypes { get { return _context.TipoNegocio.OrderBy("it.Nome"); } }

        public ObjectQuery<ConfigurarDealer> ActiveDealerSetups { get { return _context.ConfigurarDealer.Where("it.activo = true").OrderBy("it.Nome"); } }

        public ObjectQuery<ConfigurarDealer> AllDealerSetups { get { return _context.ConfigurarDealer.OrderBy("it.Nome"); } }

        public ObjectQuery<Cliente> Clients { get { return _context.Cliente.OrderBy("it.Nome"); } }

        public ObjectQuery<Negocio> Business { get { return _context.Negocio.OrderBy("it.IdNegocio"); } }

        public ObjectQuery<Tarifario> Tariffs { get { return _context.Tarifario.OrderBy("it.Nome"); } }

        public ObjectQuery<TipoItemNegocio> BusinessItemTypes { get { return _context.TipoItemNegocio.OrderBy("it.Nome"); } }

        #endregion Properties

        #region public event

        public event EventHandler<EventArgs> DealerSetupSaved;

        public event EventHandler<EventArgs> ShowingDealerImport;

        public event EventHandler<EventArgs> ShowingDealerFilter;

        public event EventHandler<EventArgs> ClientSaved;

        public event EventHandler<EventArgs> BusinessSaved;

        public event EventHandler<EventArgs> TariffSaved;

        public event EventHandler<EventArgs> BusinessItemTypeSaved;

        #endregion

        #region Event Handlers

        private void OnDealerSetupSaved()
        {
            EventHandler<EventArgs> dealerSetupSaved = DealerSetupSaved;
            if (dealerSetupSaved != null)
                dealerSetupSaved(this, new EventArgs());
        }

        public void OnShowingDealerImport()
        {
            EventHandler<EventArgs> showingDealerImport = ShowingDealerImport;
            if (showingDealerImport != null)
                showingDealerImport(this, new EventArgs());
        }

        public void OnShowingDealerFilter()
        {
            EventHandler<EventArgs> showingDealerFilter = ShowingDealerFilter;
            if (showingDealerFilter != null)
                showingDealerFilter(this, new EventArgs());
        }

        private void OnClientSaved()
        {
            EventHandler<EventArgs> clientSaved = ClientSaved;
            if (clientSaved != null)
                clientSaved(this, new EventArgs());
        }

        private void OnBusinessSaved()
        {
            EventHandler<EventArgs> businessSaved = BusinessSaved;
            if (businessSaved != null)
                businessSaved(this, new EventArgs());
        }

        private void OnTariffSaved()
        {
            EventHandler<EventArgs> tariffSaved = TariffSaved;
            if (tariffSaved != null)
                tariffSaved(this, new EventArgs());
        }

        private void OnBusinessItemTypeSaved()
        {
            EventHandler<EventArgs> businessItemTypeSaved = BusinessItemTypeSaved;
            if (businessItemTypeSaved != null)
                businessItemTypeSaved(this, new EventArgs());
        }

        #endregion Event Handlers

        #region Private Methods

        private static string GetConnectionString(string name)
        {
            foreach (ConnectionStringSettings connectionString in ConfigurationManager.ConnectionStrings)
                if (connectionString.Name == name)
                    return connectionString.ConnectionString;
            return string.Empty;
        }

        private ConfigurarDealer GetDealerSetup(long id)
        {
            ObjectQuery<ConfigurarDealer> dealerSetups = _context.ConfigurarDealer.Where("it.IdConfiguracao == @Id", new ObjectParameter("Id", id));
            foreach (ConfigurarDealer dealerSetup in dealerSetups)
                return dealerSetup;
            return null;
        }

        private static void RemovePrivateDealerKeys(ICollection<string> columns)
        {
            if (columns.Contains("IdConfiguracao")) columns.Remove("IdConfiguracao");
            if (columns.Contains("IdDealer")) columns.Remove("IdDealer");
            if (columns.Contains("AnoMes")) columns.Remove("AnoMes");
            if (columns.Contains("LinhaNegocio")) columns.Remove("LinhaNegocio");
        }

        private TipoNegocio GetBusinessType(string name)
        {
            ObjectQuery<TipoNegocio> businessTypes = _context.TipoNegocio.Where("it.Nome == @Name", new ObjectParameter("Name", name));
            foreach (TipoNegocio businessType in businessTypes)
                return businessType;
            return null;
        }

        private void SetupColumns(VodafoneDealerDataSet dataset, string businessType)
        {
            if (businessType == string.Empty) return;
            string tableName = GetBusinessType(businessType).TabelaDealer;
            DataTable table = new DataTable();
            using (DataAccess dataAccess = new DataAccess())
                dataAccess.Fill(table, _connectionString, QDealerColumnsName, new SqlParameter("TableName", tableName));
            foreach(DataRow row in table.Rows)
            {
                string columnName = (string)row["name"];
                if(dataset.Dealer.Columns.Contains(columnName))
                    continue;
                int columnType = Convert.ToInt32(row["type"]);
                Type type = DatabaseTools.GetTypeOf(columnType);
                dataset.Dealer.Columns.Add(columnName, type);
            }            
        }

        private void FilterDealer(VodafoneDealerDataSet dataset, string businessType, string businessLine, int yearMonth)
        {
            string tableName = GetBusinessType(businessType).TabelaDealer;
            using (DataAccess dataAccess = new DataAccess())
                dataAccess.Fill(dataset.Dealer, _connectionString, string.Format(QDealerFiltered, tableName),
                    new SqlParameter("BusinessType", businessType), new SqlParameter("BusinessLine", businessLine),
                    new SqlParameter("YearMonth", yearMonth));            
        }

        #endregion

        public ObjectQuery<LinhaNegocio> GetBusinessLines(string businessType)
        {
            return _context.LinhaNegocio.Where("it.Tipo == @Type", new ObjectParameter("Type", businessType));
        }

        public EntityCollection<ConfigurarDealerColunas> GetDealerSetupColumns(int dealerSetupId)
        {
            ConfigurarDealer dealerSetup = GetDealerSetup(dealerSetupId);
            if (dealerSetup == null)
                return null;
            dealerSetup.ConfigurarDealerColunas.Load();
            return dealerSetup.ConfigurarDealerColunas;
        }

        public void SaveDealerSetup(ConfigurarDealer dealerSetup)
        {
            // Validate
            if (dealerSetup.EntityState == EntityState.Detached)
                _context.AddToConfigurarDealer(dealerSetup);
            foreach (ConfigurarDealerColunas dealerSetupColumn in dealerSetup.ConfigurarDealerColunas)
                if (dealerSetupColumn.EntityState == EntityState.Detached)
                    _context.AddToConfigurarDealerColunas(dealerSetupColumn);
            _context.SaveChanges();
            OnDealerSetupSaved();
        }

        public Collection<string> GetDealerColumns(string businessType)
        {
            if(businessType == string.Empty) return new Collection<string>();
            string tableName = GetBusinessType(businessType).TabelaDealer;
            Collection<string> columns; 
            using(DataAccess dataAccess = new DataAccess())
                columns = dataAccess.ExecuteStringList(_connectionString, QDealerColumnsName, new SqlParameter("TableName", tableName));
            RemovePrivateDealerKeys(columns);
            return columns;
        }

        public VodafoneDealerDataSet ImportDealer(int dealerSetupId, string fileName, DateTime date)
        {
            ConfigurarDealer dealerSetup = GetDealerSetup(dealerSetupId);
            int yearMonth = date.Year*100 + date.Month;
            VodafoneDealerDataSet dataSet = new VodafoneDealerDataSet();
            SetupColumns(dataSet, dealerSetup.Tipo);
            FilterDealer(dataSet, dealerSetup.Tipo, dealerSetup.LinhaNegocio, yearMonth);
            return dataSet;
        }

        public VodafoneDealerDataSet FilterDealer(string businessType, string businessLine, DateTime date)
        {
            int yearMonth = date.Year * 100 + date.Month;
            VodafoneDealerDataSet dataSet = new VodafoneDealerDataSet();
            SetupColumns(dataSet, businessType);
            FilterDealer(dataSet, businessType, businessLine, yearMonth);
            return dataSet;
        }

        public void SaveClient(Cliente client)
        {
            // Validate
            if (client.EntityState == EntityState.Detached)
                _context.AddToCliente(client);
            _context.SaveChanges();
            OnClientSaved();
        }

        public void SaveBusiness(Negocio business)
        {
            // Validate
            if (business.EntityState == EntityState.Detached)
                _context.AddToNegocio(business);
            _context.SaveChanges();
            OnBusinessSaved();
        }

        public void SaveTariff(Tarifario tariff)
        {
            if (tariff.EntityState == EntityState.Detached)
                _context.AddToTarifario(tariff);
            _context.SaveChanges();
            OnTariffSaved();
        }

        public void SaveBusinessItemType(TipoItemNegocio businessItemType)
        {
            if (businessItemType.EntityState == EntityState.Detached)
                _context.AddToTipoItemNegocio(businessItemType);
            _context.SaveChanges();
            OnBusinessItemTypeSaved();
        }
    }
}
